import tempfile
import frappe
from frappe.model.document import Document
from frappe import _
from frappe.utils import strip_html
from frappe.translate import print_language
import os, json, re, openpyxl
from openpyxl.drawing.image import Image
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side, Protection # Assuming Protection is also used
from io import BytesIO
from copy import copy
from bs4 import BeautifulSoup
import subprocess
import platform
import gc
import psutil
import resource
import time

# 安装依赖库（在第一次运行时自动安装）
try:
    import psutil
except ImportError:
    frappe.msgprint(_("Installing required dependencies..."))
    subprocess.run(['pip', 'install', 'psutil', 'Pillow'], check=True)
    import psutil
    from PIL import Image as PILImage

logged_error = set()    # 避免记录重复错误，超量记录错误

# 替换系统的strip_html函数, 保留换行功能
def html_to_plain_text(html_content):
    # 使用BeautifulSoup提取纯文本
    soup = BeautifulSoup(html_content, "html.parser")
    plain_text = soup.get_text(separator="\n")  # 保留换行符
    return plain_text.strip()

# 内存使用监控函数
def log_memory_usage(context):
    """记录内存使用情况"""
    try:
        process = psutil.Process(os.getpid())
        mem_info = process.memory_info()
        frappe.logger().info(
            f"Memory usage at {context}: "
            f"RSS={mem_info.rss/1024/1024:.2f}MB, "
            f"VMS={mem_info.vms/1024/1024:.2f}MB"
        )
    except Exception as e:
        frappe.log_error(f"Memory logging failed: {str(e)}", "Memory Monitor")

def is_memory_critical(threshold=0.85):
    """检查内存使用是否接近临界值"""
    try:
        mem = psutil.virtual_memory()
        return mem.percent > threshold * 100
    except Exception:
        return False

def compress_image(original_path, max_size=2000, quality=70):
    """压缩图片以减少内存占用"""
    try:
        from PIL import Image as PILImage
        
        # 创建临时文件
        temp_dir = frappe.get_site_path("private", "temp_images")
        os.makedirs(temp_dir, exist_ok=True)
        compressed_path = os.path.join(temp_dir, f"compressed_{int(time.time())}_{os.path.basename(original_path)}")
        
        # 打开并压缩图片
        with PILImage.open(original_path) as img:
            if img.mode != 'RGB':
                img = img.convert('RGB')
                
            # 保持原始宽高比
            img.thumbnail((max_size, max_size), PILImage.LANCZOS)
            
            # 保存压缩后的图片
            img.save(compressed_path, quality=quality, optimize=True)
            
        return compressed_path
    except Exception as e:
        frappe.log_error(f"Image compression failed: {str(e)}", "Image Compression")
        return original_path  # 压缩失败时返回原路径

@frappe.whitelist()
def export_data(doc, template_file, template_name='', export_file_name='', language=None, async_export=False):
    try:
        if async_export:
            # 异步处理
            frappe.enqueue(
                "_execute_export_data",
                queue="long",
                timeout=600,
                doc=doc,
                template_file=template_file,
                template_name=template_name,
                export_file_name=export_file_name,
                language=language
            )
            return {"status": "queued", "message": _("Export started in background. You will be notified when it's ready.")}
        else:
            return _execute_export_data(doc, template_file, template_name, export_file_name, language)
    except Exception as e:        
        traceback = frappe.get_traceback(with_context=True)
        frappe.log_error("export excel open template file error", traceback)
        frappe.msgprint(f"因错误： {str(e)} 单据未能正常导出，请联络管理员查看错误日志(Error Log)了解详情")

def _execute_export_data(doc, template_file, template_name, export_file_name, language):
    """实际执行导出操作的内部函数"""
    try:
        log_memory_usage("Start export_data")
        
        if isinstance(doc, str):
            doc = json.loads(doc)

        if isinstance(doc, dict):
            if 'doctype' in doc:
                doc = frappe.get_doc(doc)
            else:
                doc = frappe._dict(doc)            
        
        if isinstance(doc, Document):
            print_settings = frappe.get_single("Print Settings").as_dict()          
            doc.run_method("before_print", print_settings)
        bench_path = frappe.utils.get_bench_path()
        template_file = template_file if template_file[:9] =='/private/' else f"/public{template_file}"
        path=f"{bench_path}/sites/{frappe.local.site}{template_file}"
        
        log_memory_usage(f"Before loading template: {path}")
        
        if path[-5:] == '.docx':
            temp_file = process_docxtpl(path, doc)
        else:
            temp_file = openpyxl.load_workbook(filename = path)
            for ws in temp_file.worksheets:
                process_worksheet(ws, doc, language, bench_path)
                apply_vertical_merges(ws) # Call the new merge function per sheet
        
        log_memory_usage("After processing template")
        
        in_memory_file = BytesIO()
        temp_file.save(in_memory_file)
        file_bytes = in_memory_file.getvalue()
        
        log_memory_usage("After saving to memory")
        
        filename = f'{template_name}_{doc.get("name")}.{path[-4:]}'
        if export_file_name:
            try:
                filename = frappe.render_template(export_file_name, {'doc':doc, 'template_name':template_name, 'path': path})
            except Exception as e:                
                frappe.msgprint(_("Export file name incorrect, error message {0}, default filename used instead").format(str(e)))

        frappe.response['filename'] = filename
        frappe.response['filecontent'] = file_bytes
        frappe.response['type'] = 'binary'
        
        log_memory_usage("End export_data")
    except Exception as e:
        traceback = frappe.get_traceback(with_context=True)
        frappe.log_error("export excel open template file error", traceback)
        frappe.msgprint(f"因错误： {str(e)} 单据未能正常导出，请联络管理员查看错误日志(Error Log)了解详情")
        raise
 
def docx_to_pdf(docx_bytes):
    from docx import Document
    from html2pdf import converter

    try:
        doc = Document(docx_bytes)
        # Convert docx to HTML
        html = doc.styles.default_font.element.xml
        for paragraph in doc.paragraphs:
            html += paragraph.element.xml
        for table in doc.tables:
            html += table.element.xml

        # Create a temporary HTML file
        with tempfile.NamedTemporaryFile(suffix='.html', delete=False) as html_file:
            tmp_html_path = html_file.name 
            html_file.write(html.encode('utf-8'))

        # Convert HTML to PDF
        with tempfile.NamedTemporaryFile(suffix='.pdf', delete=False) as pdf_file:
            tmp_pdf_path = pdf_file.name 
            converter.convert(html_file.name, pdf_file.name)

        with open(tmp_pdf_path, "rb") as f:
            pdf_bytes = f.read()
    except (IOError, docx.DocumentError):
        traceback = frappe.get_traceback(with_context=True)
        frappe.log_error("export excel open template file error", traceback)        
    finally:
        if os.path.exists(tmp_html_path):
            os.remove(tmp_html_path)  
        if os.path.exists(tmp_pdf_path):
            os.remove(tmp_pdf_path)  

    return pdf_bytes

def process_docxtpl(template_file_name, doc, language=None, bench_path=None):
    from docxtpl import DocxTemplate

    tpl = DocxTemplate(template_file_name)
    #模板中支持{{字段名}} 或 {{doc.字段名}} 访问字段变量值
    context = doc.as_dict()
    context['doc'] = doc
    tpl.render(context, frappe.get_jenv())

    return tpl

def process_worksheet(ws, doc, language=None, bench_path=None):
    log_memory_usage("Start process_worksheet")
    
    # 限制处理范围
    max_row, max_column = find_max_data_row_and_column(ws)
    max_row = min(max_row, 200)  # 绝对最大行数
    max_column = min(max_column, 50)  # 绝对最大列数
    
    # 分批处理数据
    batch_size = 50
    for batch_start in range(1, max_row + 1, batch_size):
        batch_end = min(batch_start + batch_size - 1, max_row)
        # 修复：传递当前批次结束行，允许函数内部修改
        batch_end = _process_worksheet_batch(ws, doc, language, bench_path, batch_start, batch_end, max_column)
        
        # 每处理一个批次后检查内存
        if is_memory_critical():
            frappe.logger().warning("Memory critical during processing, forcing GC")
            gc.collect()
            
    log_memory_usage("End process_worksheet")

def _get_row_comments(ws, row_idx, max_column):
    """Pre-fetches and returns comments for all cells in a given row."""
    row_comments = {}
    for j_idx in range(1, max_column + 1):
        cell_at_j_idx = ws.cell(row=row_idx, column=j_idx)
        if cell_at_j_idx.comment:
            row_comments[j_idx] = cell_at_j_idx.comment.text.strip().lower()
    return row_comments

def _cache_and_clear_style_conditions(ws, template_row_idx, max_column, current_row_comments, logged_error):
    """
    Scans the template_row_idx for style_cond comments, caches their rules,
    and clears the comments from the template cells to prevent copying.
    Returns the dictionary of cached style conditions.
    """
    cached_style_conditions = {}
    for k in range(1, max_column + 1):
        template_cell_k = ws.cell(row=template_row_idx, column=k)
        temp_comment = template_cell_k.comment
        temp_comment_text_stripped_lower_k = temp_comment.text.strip().lower() if temp_comment else ""

        if temp_comment_text_stripped_lower_k.startswith("style_cond:"):
            try:
                parts = temp_comment_text_stripped_lower_k.split(':', 2)
                if len(parts) == 3:
                    condition_str = parts[1].strip()
                    styles_str = parts[2].strip()
                    cached_style_conditions[k] = (condition_str, styles_str)
                    # IMPORTANT: Clear the comment from the template cell immediately.
                    # This prevents insert_rows_with_copy from copying it.
                    template_cell_k.comment = None
                    # Also clear from the current_row_comments cache if it was there
                    if k in current_row_comments:
                        current_row_comments[k] = ""
            except Exception as e:
                frappe.log_error(
                    f"Error caching style_cond comment: {temp_comment_text_stripped_lower_k} - {str(e)}",
                    "Excel Export Style"
                )
                template_cell_k.comment = None # Clear even on error
                if k in current_row_comments:
                    current_row_comments[k] = "" # Clear from cache
    return cached_style_conditions

def _handle_row_directive(ws, current_row_idx, max_column, doc, current_sheet_end_row, current_row_comments, logged_error):
    """
    Handles the 'row=' directive: evaluates the expression, caches style conditions,
    inserts new rows, and updates the sheet's effective end row.
    Returns (list of row data, updated sheet end row, cached style conditions for the block).
    """
    cell_a1 = ws.cell(row=current_row_idx, column=1)
    row_expression = cell_a1.comment.text.strip()[4:] # Use original cell.comment for its text here

    rows_data = []
    num_rows_to_insert = 0
    cached_style_conditions = {}

    try:
        rows_data = frappe.safe_eval(row_expression, None, {"doc": doc})

        # Cache style_cond comments from this template row and clear them
        cached_style_conditions = _cache_and_clear_style_conditions(
            ws, current_row_idx, max_column, current_row_comments, logged_error
        )

        cell_a1.comment = None # Clear 'row=' comment from this A column cell
        current_row_comments[1] = "" # Also clear from current_row_comments cache

        rows_count = len(rows_data)
        if rows_count > 1:
            if rows_count > 1500: # Max rows limit
                frappe.throw(_("Too many rows to insert (max 1500 allowed)"))

            num_rows_to_insert = rows_count - 1
            # insert_rows_with_copy should now copy cells WITHOUT the style_cond comments
            insert_rows_with_copy(ws, current_row_idx + 1, num_rows_to_insert)
            current_sheet_end_row += num_rows_to_insert

            # This loop clears comments from column 1 of newly inserted rows (for copied 'row=' comments)
            for r_idx_new in range(current_row_idx + 1, current_row_idx + 1 + num_rows_to_insert):
                new_cell = ws.cell(row=r_idx_new, column=1)
                if new_cell.comment:
                    new_cell.comment = None

    except Exception as e:
        frappe.log_error(f"Error evaluating row expression: {row_expression}", "Excel Export")
        rows_data = [] # Ensure rows_data is empty if eval fails
        cached_style_conditions = {} # Reset cache on error

    return rows_data, current_sheet_end_row, cached_style_conditions

def _render_cell_content(ws, cell, text, doc, current_row_data, bench_path, logged_error, pre_row_cell_value=None):
    """
    Renders template strings in a cell, handles image paths, and converts HTML.
    Updates cell.value directly.
    """
    if not text or not has_variable_string(text):
        return # No variable to render

    try:
        value = frappe.render_template(text, {'doc': doc, 'row': current_row_data})
        if value and isinstance(value, str) and value.startswith("/") and value.lower().endswith(('.jpg', '.png', '.jpeg')):
            # same image, skip add_image in the following rows
            #frappe.log_error('excel export debug prev row', f"text={text}, {pre_row_cell_value}, {value}")            
            if pre_row_cell_value and pre_row_cell_value == value:
                cell.value = '' # Clear cell value if image added
                return value

            file_path = value if value.startswith('/private/') else f'/public{value}'
            abs_path = f"{bench_path}/sites/{frappe.local.site}{file_path}"

            if not os.path.exists(abs_path):
                frappe.logger().warning(f"Image file not found: {abs_path}")
                cell.value = "[Image Missing]"
            else:
                add_image(ws, cell.row, cell.column, abs_path)
                cell.value = '' # Clear cell value if image added
        else:
            value = html_to_plain_text(value or '').replace('None', '')
            cell.value = text if "{{ no such element:" in value else value
        
        return value
    except Exception as e:
        if text not in logged_error and len(logged_error) < 10:
            logged_error.add(text)
            traceback = frappe.get_traceback(with_context=True)
            frappe.log_error("export excel render template error", traceback)
            cell.value = f"[Error: {str(e)}]"

def _apply_style_condition(cell, condition_str, styles_str, logged_error):
    """
    Evaluates a style condition (e.g., len>) and applies specified font styles to a cell.
    """
    apply_style = False
    if condition_str.startswith("len>"):
        try:
            threshold = int(condition_str[4:])
            if len(str(cell.value or '')) > threshold:
                apply_style = True
        except ValueError:
            frappe.log_error(f"Invalid length threshold in style condition: {condition_str}", "Excel Export Style")
            return # Don't apply style on malformed condition

    if apply_style:
        # Create a copy of the existing font or a new Font object if none exists
        current_font = copy(cell.font) if cell.font else Font()

        styles_list = [s.strip() for s in styles_str.split(',')]
        for style_item in styles_list:
            if style_item == "bold":
                current_font.bold = True
            elif style_item.startswith("size="):
                try:
                    current_font.size = int(style_item[5:])
                except ValueError:
                    frappe.log_error(f"Invalid font size in style: {style_item}", "Excel Export Style")
        cell.font = current_font
    

def _process_worksheet_batch(ws, doc, language, bench_path, start_row, end_row, max_column):
    """
    Processes a batch of rows in a worksheet, handling template rendering,
    row expansion via 'row=' comments, and conditional styling.
    """
    # Variables to manage repeating data blocks
    current_block_rows_data = [] # List of data rows for the current 'row=' block
    current_block_data_index = -1 # Index of the current data row being processed within current_block_rows_data
    current_evaluated_row_data = frappe._dict() # The actual data dict for the current row (from doc or block data)

    # Tracks the effective last row on the sheet, which can grow due to row expansion
    current_sheet_end_row = end_row

    # Dictionary to store style_cond rules, populated when a 'row=' instruction is found
    active_template_row_style_conditions = {}

    # Set to store errors once to avoid repeated logging for the same error in template
    logged_error = set()

    with print_language(language):
        row_idx = start_row
        last_row_value = {} #track the last row value, in order to prevent output same image the following row
        while row_idx <= current_sheet_end_row:
            # Memory usage logging and garbage collection
            if row_idx % 10 == 0:
                log_memory_usage(f"Processing row {row_idx}")
                if is_memory_critical():
                    frappe.logger().warning("Memory critical during processing, forcing GC")
                    gc.collect()

            # Pre-fetch comments for the current physical row on the sheet
            # This is an optimization for the current row's processing.
            current_physical_row_comments = _get_row_comments(ws, row_idx, max_column)

            # Flag to indicate if the current row (row_idx) triggered a new 'row=' block expansion.
            # This is important for resetting block variables *after* all iterations of the current block.
            is_new_block_start = False

            # Iterate through columns of the current row            
            for col_idx in range(1, max_column + 1):
                cell = ws.cell(row=row_idx, column=col_idx)
                comment_text_lower = current_physical_row_comments.get(col_idx, "")

                # 1. Handle 'row=' comment (always in column A, highest priority)
                if col_idx == 1 and comment_text_lower.startswith("row="):
                    is_new_block_start = True
                    current_block_rows_data, current_sheet_end_row, active_template_row_style_conditions = \
                        _handle_row_directive(ws, row_idx, max_column, doc, current_sheet_end_row, current_physical_row_comments, logged_error)
                    current_block_data_index = -1 # Reset data index for the new block

                # 2. Update context for template rendering (`row` variable)
                # This only happens for the first column (A) as it defines the row's data context.
                if col_idx == 1:
                    if current_block_rows_data and (current_block_data_index + 1) < len(current_block_rows_data):
                        current_block_data_index += 1
                        current_evaluated_row_data = current_block_rows_data[current_block_data_index]
                    else:
                        # If not inside a repeating block, or block finished, use the main doc
                        current_evaluated_row_data = frappe._dict() # Reset if block finished
                        # The original code implicitly used `doc` when `row` was empty.
                        # For now, we rely on `frappe.render_template` handling `doc` when `row` is empty.

                # 3. Render cell values

                last_row_value[col_idx] = _render_cell_content(ws, cell, str(cell.value or ''), doc, current_evaluated_row_data, bench_path, 
                    logged_error, pre_row_cell_value=last_row_value.get(col_idx)
                )

                # 4. Apply conditional styles
                if active_template_row_style_conditions and col_idx in active_template_row_style_conditions:
                    condition_str, styles_str = active_template_row_style_conditions[col_idx]
                    try:
                        _apply_style_condition(cell, condition_str, styles_str, logged_error)
                    except Exception as e:
                        frappe.log_error(f"Error applying cached style_cond: {condition_str}, {styles_str} - {str(e)}", "Excel Export Style")

            # 5. Reset block-related variables if the current repeating block has completed all its iterations.
            # This check ensures we don't reset when we just *started* a new block.
            if not is_new_block_start and current_block_rows_data and (current_block_data_index + 1) >= len(current_block_rows_data):
                current_block_rows_data = []
                current_block_data_index = -1
                current_evaluated_row_data = frappe._dict() # Clear data context
                active_template_row_style_conditions = {} # Reset for future blocks

            row_idx += 1
    return current_sheet_end_row

def add_image(ws, row, column, path, width=0, height=0, scale=0.9):
    try:
        # Check file size and compress if larger than 1MB
        if os.path.exists(path) and os.path.getsize(path) > 1024 * 1024:  # 1MB
            compressed_path = compress_image(path)
            if compressed_path:
                path = compressed_path
                frappe.logger().info(f"Using compressed image: {path}")

        # Load the image
        img = Image(path)

        # Check if the cell is part of a merged cell range
        merged_range = None
        for merged_cell in ws.merged_cells.ranges:
            if merged_cell.min_row <= row <= merged_cell.max_row and merged_cell.min_col <= column <= merged_cell.max_col:
                merged_range = merged_cell
                break

        # Define conversion factors
        COL_PX_FACTOR = 64 / 8.43  # ≈7.59, based on default column width 8.43 units = 64 pixels
        ROW_PX_FACTOR = 96 / 72    # ≈1.333, assuming 96 DPI
        PADDING_PX = 2  # Small padding to account for borders/gridlines

        if merged_range:
            # Calculate total width and height of the merged cell range in pixels
            total_width_px = 0
            total_height_px = 0

            # Sum the widths of all columns in the merged range
            for col_idx in range(merged_range.min_col, merged_range.max_col + 1):
                col_letter = get_column_letter(col_idx)
                col_width = ws.column_dimensions[col_letter].width
                col_width = col_width if col_width else 8.43  # Default
                total_width_px += col_width * COL_PX_FACTOR

            # Sum the heights of all rows in the merged range
            for row_idx in range(merged_range.min_row, merged_range.max_row + 1):
                row_height = ws.row_dimensions[row_idx].height
                row_height = row_height if row_height else 15  # Default
                total_height_px += row_height * ROW_PX_FACTOR

            # Apply padding
            total_width_px = max(total_width_px - PADDING_PX * 2, 0)
            total_height_px = max(total_height_px - PADDING_PX * 2, 0)

        else:
            # Single cell case
            column_letter = get_column_letter(column)
            col_width = ws.column_dimensions[column_letter].width
            row_height = ws.row_dimensions[row].height
            col_width = col_width if col_width else 8.43
            row_height = row_height if row_height else 15
            total_width_px = col_width * COL_PX_FACTOR - PADDING_PX * 2
            total_height_px = row_height * ROW_PX_FACTOR - PADDING_PX * 2

        # If width and height are explicitly provided, use them
        if width and height:
            img.width = width
            img.height = height
        else:
            # Calculate scaling to fit image within the cell or merged cell range
            img_width_px = img.width
            img_height_px = img.height
            width_ratio = total_width_px / img_width_px
            height_ratio = total_height_px / img_height_px
            resize_ratio = min(width_ratio, height_ratio) * scale  # Apply user-defined scale factor

            # Apply the scaling
            img.width = img_width_px * resize_ratio
            img.height = img_height_px * resize_ratio

        # Log dimensions for debugging
        frappe.logger().info(
            f"Image at {get_column_letter(column)}{row}: "
            f"Merged range={merged_range}, "
            f"Cell dimensions={total_width_px:.2f}x{total_height_px:.2f}px, "
            f"Original image={img_width_px}x{img_height_px}px, "
            f"Scaled image={img.width:.2f}x{img.height:.2f}px"
        )

        # Set the image anchor to the top-left corner of the cell or merged cell
        anchor_cell = f"{get_column_letter(column)}{row}"
        if merged_range:
            anchor_cell = f"{get_column_letter(merged_range.min_col)}{merged_range.min_row}"
        img.anchor = anchor_cell

        # Add the image to the worksheet
        ws.add_image(img)

    except Exception as e:
        if path not in logged_error and len(logged_error) < 10:
            logged_error.add(path)
            traceback = frappe.get_traceback(with_context=True)
            frappe.log_error(f"Export excel add image error: {str(e)}", traceback)
        ws.cell(row=row, column=column).value = f"[Image Error: {os.path.basename(path)}]"

compiled_pattern = re.compile(r'\{\{(?=.*?\}\})')

def has_variable_string(s):
    """at least one occurance of {{变量名}}"""

    if isinstance(s, str):
        return bool(compiled_pattern.search(s))  

def copy_style(source_cell, target_cell):
    """
    Copies cell style and properties from source to target, EXCLUDING COMMENTS.
    Comments (specifically style_cond) are now handled separately by caching them.
    """
    target_cell.font = copy(source_cell.font) if source_cell.font else Font()
    target_cell.alignment = copy(source_cell.alignment) if source_cell.alignment else Alignment()
    target_cell.border = copy(source_cell.border) if source_cell.border else Border()
    target_cell.fill = copy(source_cell.fill) if source_cell.fill else PatternFill()
    target_cell.number_format = source_cell.number_format
    target_cell.protection = copy(source_cell.protection) if source_cell.protection else Protection()

def insert_rows_with_copy(ws, row_index, num_rows_to_insert):
    """
    Inserts multiple rows at the specified row index and copies the content and style,
    including row height, of the row above.
    """

    # Handle merged cells below the insertion point
    merged_cells_range = ws.merged_cells.ranges
    for merged_cell in merged_cells_range:
        if merged_cell.min_row >= row_index:
            merged_cell.shift(0, num_rows_to_insert)

    source_row = row_index - 1
    ws.insert_rows(row_index, num_rows_to_insert)

    # Only copy the first 50 columns' styles (adjust as needed)
    max_col = min(50, ws.max_column)

    for i in range(num_rows_to_insert):
        target_row = row_index + i

        # **ADD THIS LINE TO COPY ROW HEIGHT**
        if source_row in ws.row_dimensions:
            ws.row_dimensions[target_row].height = ws.row_dimensions[source_row].height

        # Copy values and cell styles
        for col_idx in range(1, max_col + 1):
            src_cell = ws.cell(row=source_row, column=col_idx)
            dest_cell = ws.cell(row=target_row, column=col_idx)

            # Copy value
            dest_cell.value = src_cell.value

            # Copy style
            copy_style(src_cell, dest_cell)

        # Copy merged cells
        copy_merged_cells(ws, source_row, target_row)

def clear_merged_cells_on_row(worksheet, row_number):
    merged_cells_copy = list(worksheet.merged_cells.ranges)
    for merged_cell in merged_cells_copy:
        min_row, max_row = merged_cell.min_row, merged_cell.max_row
        if min_row == row_number:
            worksheet.unmerge_cells(str(merged_cell))

def copy_merged_cells(worksheet, source_row, target_row):
    merged_cells_copy = list(worksheet.merged_cells.ranges)
    for merged_cell in merged_cells_copy:
        min_col, max_col, min_row, max_row = merged_cell.min_col, merged_cell.max_col, merged_cell.min_row, merged_cell.max_row
        if min_row == source_row and max_row == source_row:
            new_merged_cell = f"{get_column_letter(min_col)}{target_row}:{get_column_letter(max_col)}{target_row}"
            worksheet.merge_cells(new_merged_cell)

def find_max_data_row_and_column(ws):
    """最多1000行，100列，优化性能"""

    max_row = 0  
    max_column = 0  
    
    # 限制检查范围
    for row in ws.iter_rows(min_row=1, max_col=50, max_row=200):  
        for cell in row:  
            if cell.value and (isinstance(cell.value, str) and cell.value.strip() != '' or not isinstance(cell.value, str)):                  
                max_row = max(max_row, cell.row)  
                max_column = max(max_column, cell.column)
                
    # 安全限制
    max_row = min(max_row, 200)
    max_column = min(max_column, 50)
    
    return max_row, max_column

@frappe.whitelist()
def get_export_excel_template():
    return frappe.get_all("Excel Export Template",
        filters = {'template_file': ['is','set']},
        fields = ['template_name','template_file', 'export_file_name', 'condition',
            'is_multi_language', 'reference_doctype', 'enable_export_excel', 'enable_export_pdf']
    )

@frappe.whitelist()
def export_pdf(doc, template_file, template_name='', export_file_name='', language=None, async_export=False):
    try:
        if async_export:
            frappe.enqueue(
                "_execute_export_pdf",
                queue="long",
                timeout=600,
                doc=doc,
                template_file=template_file,
                template_name=template_name,
                export_file_name=export_file_name,
                language=language
            )
            return {"status": "queued", "message": _("PDF export started in background. You will be notified when it's ready.")}
        else:
            return _execute_export_pdf(doc, template_file, template_name, export_file_name, language)
    except Exception as e:
        traceback = frappe.get_traceback(with_context=True)
        frappe.log_error(f"Export PDF error: {str(e)}\n{traceback}", "PDF Export Error")
        frappe.msgprint(f"因错误： {str(e)} 单据未能正常导出，请联络管理员查看错误日志(Error Log)了解详情")

def _execute_export_pdf(doc, template_file, template_name, export_file_name, language):
    """实际执行PDF导出的内部函数"""
    try:
        # 检查LibreOffice是否安装
        if not is_libreoffice_installed():
            install_libreoffice()
        
        # 首先生成 Excel/Word 文件
        if isinstance(doc, str):
            doc = json.loads(doc)

        if isinstance(doc, dict):
            if 'doctype' in doc:
                doc = frappe.get_doc(doc)
            else:
                doc = frappe._dict(doc)

        if isinstance(doc, Document):    
            print_settings = frappe.get_single("Print Settings").as_dict()    
            doc.run_method("before_print", print_settings)

        bench_path = frappe.utils.get_bench_path()
        template_file = template_file if template_file[:9] =='/private/' else f"/public{template_file}"
        path = f"{bench_path}/sites/{frappe.local.site}{template_file}"
        
        # 创建临时目录
        temp_dir = os.path.join(os.path.expanduser('~'), '.temp_pdf_export')
        os.makedirs(temp_dir, exist_ok=True)
        
        try:
            file_type = os.path.splitext(path)[1].lower()
            source_file = os.path.join(temp_dir, f"temp{file_type}")
            
            # 生成源文件（Excel或Word）
            if file_type == '.docx':
                temp_file = process_docxtpl(path, doc)
                temp_file.save(source_file)
            else:
                # Excel文件
                temp_file = openpyxl.load_workbook(filename=path)
                for ws in temp_file.worksheets:
                    process_worksheet(ws, doc, language, bench_path)
                temp_file.save(source_file)
            
            # 使用LibreOffice转换为PDF
            pdf_file = os.path.join(temp_dir, 'output.pdf')
            
            # 使用soffice命令进行转换
            try:
                # 添加更多转换选项以提高兼容性
                cmd = [
                    'soffice',
                    '--headless',
                    '--norestore',
                    '--nofirststartwizard',
                    '--nologo',
                    '--convert-to', 'pdf:writer_pdf_Export',
                    '--outdir', temp_dir,
                    source_file
                ]
                
                process = subprocess.run(
                    cmd,
                    capture_output=True,
                    text=True,
                    timeout=300  # 设置更长的超时时间（5分钟）
                )
                
                # 检查转换结果
                if process.returncode != 0:
                    error_msg = f"PDF conversion failed. Exit code: {process.returncode}\nError output: {process.stderr}\nStandard output: {process.stdout}"
                    frappe.log_error(error_msg, "PDF Export Error")
                    
                    # 尝试提供更具体的错误信息
                    if "error" in process.stderr.lower() or "error" in process.stdout.lower():
                        user_msg = _("PDF conversion failed due to an internal error. Please try a different template or contact support.")
                    elif "timeout" in process.stderr.lower():
                        user_msg = _("PDF conversion timed out. The document may be too complex. Please try a simpler template.")
                    elif "not found" in process.stderr.lower():
                        user_msg = _("LibreOffice not found or not properly installed. Please check installation.")
                    else:
                        user_msg = _("PDF conversion failed. Please check the error log for details.")
                    
                    frappe.throw(user_msg)
            except subprocess.TimeoutExpired:
                frappe.throw(_("PDF conversion took too long. The document may be too complex. Please try a simpler template or export as Excel/Word instead."))
            
            # 获取生成的PDF文件名 - LibreOffice使用输入文件名但扩展名为.pdf
            base_name = os.path.splitext(os.path.basename(source_file))[0]
            pdf_file = os.path.join(temp_dir, f"{base_name}.pdf")
            
            # 如果找不到默认名称的PDF文件，尝试其他可能的名称
            if not os.path.exists(pdf_file):
                # LibreOffice有时使用"output.pdf"作为输出文件名
                alt_pdf_file = os.path.join(temp_dir, 'output.pdf')
                if os.path.exists(alt_pdf_file):
                    pdf_file = alt_pdf_file
                else:
                    # 列出目录中所有的PDF文件
                    pdf_files = [f for f in os.listdir(temp_dir) if f.lower().endswith('.pdf')]
                    if pdf_files:
                        pdf_file = os.path.join(temp_dir, pdf_files[0])
            
            if not os.path.exists(pdf_file):
                error_msg = f"PDF file not found in temporary directory: {temp_dir}\nContents: {os.listdir(temp_dir)}"
                frappe.log_error(error_msg, "PDF Export Error")
                
                # 尝试备选方案
                if file_type == '.docx':
                    frappe.msgprint(_("Using fallback method for Word conversion, formatting may be affected"))
                    with open(source_file, "rb") as f:
                        docx_bytes = f.read()
                    pdf_content = docx_to_pdf(docx_bytes)
                else:
                    # 对于Excel文件，返回原始Excel文件并提示用户
                    frappe.msgprint(_("PDF conversion failed. Returning the Excel file instead. Please convert it manually."))
                    with open(source_file, "rb") as f:
                        pdf_content = f.read()
                    # 修改文件名为Excel
                    filename = f'{template_name}_{doc.get("name")}.xlsx'
                    if export_file_name:
                        try:
                            filename = frappe.render_template(export_file_name, {'doc':doc, 'template_name':template_name}) + '.xlsx'
                        except Exception as e:
                            frappe.msgprint(_("Export file name incorrect, error message {0}, default filename used instead").format(str(e)))
                    
                    frappe.response['filename'] = filename
                    frappe.response['filecontent'] = pdf_content
                    frappe.response['type'] = 'binary'
                    return
            else:
                # 读取生成的PDF文件
                with open(pdf_file, 'rb') as f:
                    pdf_content = f.read()
                
        finally:
            # 清理临时文件
            try:
                import shutil
                shutil.rmtree(temp_dir)
            except Exception as e:
                frappe.log_error(f"Cleanup error: {str(e)}", "PDF Export Warning")

        # 设置响应
        filename = f'{template_name}_{doc.get("name")}.pdf'
        if export_file_name:
            try:
                filename = frappe.render_template(export_file_name, {'doc':doc, 'template_name':template_name}) + '.pdf'
            except Exception as e:
                frappe.msgprint(_("Export file name incorrect, error message {0}, default filename used instead").format(str(e)))

        frappe.response['filename'] = filename
        frappe.response['filecontent'] = pdf_content
        frappe.response['type'] = 'binary'
        
    except Exception as e:
        traceback = frappe.get_traceback(with_context=True)
        frappe.log_error(f"Export PDF error: {str(e)}\n{traceback}", "PDF Export Error")
        frappe.msgprint(f"因错误： {str(e)} 单据未能正常导出，请联络管理员查看错误日志(Error Log)了解详情")
        raise

def is_libreoffice_installed():
    """检查系统是否安装了LibreOffice"""
    try:
        # 检查Windows系统
        if platform.system() == "Windows":
            # 检查常见安装路径
            paths = [
                os.path.join(os.environ.get('PROGRAMFILES', 'C:\\Program Files'), 'LibreOffice', 'program', 'soffice.exe'),
                os.path.join(os.environ.get('PROGRAMFILES(X86)', 'C:\\Program Files (x86)'), 'LibreOffice', 'program', 'soffice.exe')
            ]
            return any(os.path.exists(path) for path in paths)
        
        # 检查Linux/macOS系统
        result = subprocess.run(['which', 'soffice'], capture_output=True, text=True)
        return result.returncode == 0
    except Exception:
        return False

def install_libreoffice():
    """安装LibreOffice"""
    try:
        if is_libreoffice_installed():
            return True
            
        frappe.msgprint(_("LibreOffice not found, attempting to install... (This may take a few minutes)"))
        
        # 获取操作系统信息
        os_name = platform.system()
        
        # 根据操作系统执行不同的安装命令
        if os_name == "Linux":
            # 检测Linux发行版
            if os.path.exists('/etc/debian_version'):
                # Debian/Ubuntu系统
                subprocess.run(['sudo', 'apt-get', 'update'], check=True)
                subprocess.run(['sudo', 'apt-get', 'install', '-y', 'libreoffice'], check=True)
            elif os.path.exists('/etc/redhat-release'):
                # CentOS/RHEL系统
                subprocess.run(['sudo', 'yum', 'install', '-y', 'libreoffice'], check=True)
            else:
                frappe.throw(_("Unsupported Linux distribution. Please install LibreOffice manually."))
        
        elif os_name == "Windows":
            # 下载并安装LibreOffice
            frappe.throw(_("Please install LibreOffice manually on Windows: https://www.libreoffice.org/download/download-libreoffice/"))
        
        elif os_name == "Darwin":  # macOS
            # 使用Homebrew安装
            subprocess.run(['brew', 'install', '--cask', 'libreoffice'], check=True)
        
        else:
            frappe.throw(_("Unsupported operating system. Please install LibreOffice manually."))
        
        # 验证安装
        if is_libreoffice_installed():
            frappe.msgprint(_("LibreOffice installed successfully!"))
            return True
        else:
            frappe.log_error("LibreOffice installation failed", "PDF Export Error")
            frappe.throw(_("LibreOffice installation failed. Please install it manually."))
            return False
    except subprocess.CalledProcessError as e:
        frappe.log_error(f"LibreOffice installation failed: {str(e)}", "PDF Export Error")
        frappe.throw(_("LibreOffice installation failed: {0}. Please install it manually.").format(str(e)))
    except Exception as e:
        frappe.log_error(f"Unexpected error during LibreOffice installation: {str(e)}", "PDF Export Error")
        frappe.throw(_("Unexpected error during LibreOffice installation: {0}. Please install it manually.").format(str(e)))

def apply_vertical_merges(ws):
    """
    Applies vertical merges to columns marked with 'merge_v:true' comment.
    This function should be called after all data processing for the worksheet is complete.
    """
    frappe.logger().debug(f"Start apply_vertical_merges for sheet: {ws.title}")

    merge_columns = []
    # Check first 5 rows for the config comment.
    # Given 'one cell, one comment, one instruction', we expect only 'merge_v:true'
    for col_idx in range(1, ws.max_column + 1):
        for r_idx in range(1, min(ws.max_row + 1, 5)):
            cell = ws.cell(row=r_idx, column=col_idx)
            if cell.comment:
                comment_text_stripped_lower = cell.comment.text.strip().lower()
                # Strict check: comment must be exactly "merge_v:true"
                if comment_text_stripped_lower == "merge_v:true":
                    merge_columns.append(col_idx)
                    cell.comment = None # Remove comment immediately after identification
                    break # Found for this column, move to next column

    if not merge_columns:
        frappe.logger().debug("No columns marked for vertical merge.")
        return

    for col_idx in merge_columns:
        start_merge_row = -1
        previous_value = None

        for row_idx in range(1, ws.max_row + 1):
            current_cell = ws.cell(row=row_idx, column=col_idx)
            current_value = current_cell.value

            if start_merge_row == -1:
                start_merge_row = row_idx
                previous_value = current_value
            elif current_value == previous_value:
                pass
            else:
                if row_idx - 1 > start_merge_row:
                    # Get the top-left cell of the range BEFORE merging
                    top_left_cell_for_merge = ws.cell(row=start_merge_row, column=col_idx)
                    # Set its vertical alignment to center
                    # We copy existing alignment to preserve horizontal, indentation etc.
                    current_alignment = copy(top_left_cell_for_merge.alignment) if top_left_cell_for_merge.alignment else Alignment()
                    current_alignment.vertical = "center"
                    top_left_cell_for_merge.alignment = current_alignment

                    ws.merge_cells(start_row=start_merge_row, start_column=col_idx,
                                   end_row=row_idx - 1, end_column=col_idx)
                start_merge_row = row_idx
                previous_value = current_value

        # After the loop, check if there's an unmerged block at the end of the column
        if start_merge_row != -1 and ws.max_row > start_merge_row:
            # Get the top-left cell of the final merge range
            top_left_cell_for_merge = ws.cell(row=start_merge_row, column=col_idx)
            # Set its vertical alignment to center
            current_alignment = copy(top_left_cell_for_merge.alignment) if top_left_cell_for_merge.alignment else Alignment()
            current_alignment.vertical = "center"
            top_left_cell_for_merge.alignment = current_alignment

            ws.merge_cells(start_row=start_merge_row, start_column=col_idx,
                           end_row=ws.max_row, end_column=col_idx)

    frappe.logger().debug("End apply_vertical_merges")